<?php

namespace app\admin\controller;

use app\api\controller\Common;
use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;

/**
 * 后台公共
 *
 * @icon fa fa-circle-o
 */
class AdminCommon extends Backend
{
    //导出表格
    public static function exportExcel($expTitle,$expCellName,$expTableData,$fileName){
        $topNumber = 1;//表头有几行占用
        $cellKey = array(
            'A','B','C','D','E','F','G','H','I','J','K','L','M',
            'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
            'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
        );
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle($expTitle);
//         $spreadsheet->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        //处理表头
        foreach ($expCellName as $k=>$v) {
            $sheet->setCellValue($cellKey[$k].$topNumber, $v[1]);//设置表头数据
//            $spreadsheet->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口
            $sheet->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(false);//设置是否加粗
        }
        //处理数据
        //设置单元格居中显示
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //表头居中
        foreach ($expCellName as $k2=>$v2) {
            $sheet->getStyle($cellKey[$k2].'1')->applyFromArray($styleArray);
        }
        foreach ($expTableData as $k=>$v) {
            foreach ($expCellName as $k1=>$v1) {
                $sheet->setCellValue($cellKey[$k1].($k+1+$topNumber), $v[$v1[0]]);
//                $sheet->getColumnDimension($cellKey[$k1])->setWidth(20);//每列宽度
                $sheet->getColumnDimension($cellKey[$k1])->setWidth($v1[2]);//每列宽度
                $sheet->getStyle($cellKey[$k1].($k+1+$topNumber))->applyFromArray($styleArray);
            }
        }
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
        header('Content-Disposition: attachment;filename="'.$fileName.'.xls"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xls'); //按照指定格式生成Excel文件
        $writer->save('php://output');
        exit;
    }

    //转换fastadmin 时间
    public static function getDateByFloatValue($dateValue = 0,$calendar_type = 1900){
        // Excel中的日期存储的是数值类型，计算的是从1900年1月1日到现在的数值
        if (1900 == $calendar_type) { // WINDOWS中EXCEL 日期是从1900年1月1日的基本日期
            $myBaseDate = 25569;// php是从 1970-01-01 25569是到1900-01-01所相差的天数
            if ($dateValue < 60) {
                --$myBaseDate;
            }
        } else {// MAC中EXCEL日期是从1904年1月1日的基本日期(25569-24107 = 4*365 + 2) 其中2天是润年的时间差？
            $myBaseDate = 24107;
        }

        // 执行转换
        if ($dateValue >= 1) {
            $utcDays = $dateValue - $myBaseDate;
            $returnValue = round($utcDays * 86400);
            if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
                $returnValue = (integer)$returnValue;
            }
        } else {
            // 函数对浮点数进行四舍五入
            $hours = round($dateValue * 24);
            $mins = round($dateValue * 1440) - round($hours * 60);
            $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
            $returnValue = (integer)gmmktime($hours, $mins, $secs);
        }

        return $returnValue-3600*8;// 返回时间戳
    }

    //导出套餐订单列表
    public static function exportOrderList($where){
        $expTableData = Common::selectWhereData('order',$where,'r_id,order_sn,price,content,total_num,use_num,not_use_num,advice,address,organization,pin_user,mark','order_sn asc');
        //查询手机号
        $r_ids = array_unique(array_column($expTableData,'r_id'));
        $res_mobile = Common::selectWhereData('registers',['id'=>['in',$r_ids]],'id,mobile');
        foreach($expTableData as $key => $r){
            $user_name = Db::name('registers')->where(['id'=>$r['r_id']])->value('user_name');
            $expTableData[$key]['user_name'] = $user_name;//Db::name('user')->where(['id'=>$user_id])->value('nickname');
        }
        $expCellName  = [
            ['order_sn','订单编号',15],
            ['user_name','客户姓名',15],
            ['price','订单金额',15],
            ['content','购买服务内容',20],
            ['total_num','购买次数',15],
            ['use_num','已用服务次数',15],
            ['not_use_num','剩余服务次数',15],
            ['advice','所属顾问',10],
            ['mobile','VIP手机号',20],
            ['address','详细地址',30],
            ['organization','归属机构',30],
            ['pin_user','品拓人员',15],
            ['mark','备注',40],
        ];

        foreach ($expTableData as &$v) {
            $v['mobile'] = '';
            foreach ($res_mobile as $value){
                if($v['r_id'] == $value['id']){
                    $v['mobile'] = $value['mobile'];
                }
            }
        }
        $expTitle  = "套餐订单列表";
        $fileName  = "套餐订单列表";//文件名
        self::exportExcel($expTitle,$expCellName,$expTableData,$fileName);
    }

    //导出服务订单列表
    public static function exportServiceList($where){
        $expTableData = Common::selectWhereData('service_order',$where,'r_id,order_sn,customer_name,service_time,service_content,service_name,assess,mark','order_sn asc');
        //查询手机号
        $r_ids = array_unique(array_column($expTableData,'r_id'));
        $res_mobile = Common::selectWhereData('registers',['id'=>['in',$r_ids]],'id,mobile');
        $expCellName  = [
            ['order_sn','服务订单编号',20],
            ['customer_name','客户姓名',10],
            ['service_time','服务时间',20],
            ['service_content','服务项目',30],
            ['service_name','服务员',10],
            ['mobile','VIP手机号',20],
            ['assess','评价',30],
            ['mark','备注',40],
        ];

        foreach ($expTableData as &$v) {
            $v['mobile'] = '';
            foreach ($res_mobile as $value){
                if($v['r_id'] == $value['id']){
                    $v['mobile'] = $value['mobile'];
                }
            }
            $v['service_time'] = date('Y-m-d H:i:s',$v['service_time']);
        }
        $expTitle  = "服务订单列表";
        $fileName  = "服务订单列表";//文件名
        self::exportExcel($expTitle,$expCellName,$expTableData,$fileName);
    }

}
